<?php

namespace MLM\Model\Investment;


use MLM\Model\Investment;

class Storage extends \MLM\Model\Generic\Db\Storage {

    public function getActiveInvestment($accNum){

        $data = array(1, 1);


        $sql = "select DATE_FORMAT(subs_start_dat,'%d-%m-%Y') as subs_start_dat, subs_amount
                from members where member_code = ? and subs_start_dat <= now() and subs_end_dat >= now() ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($accNum));

        $dataInvest = array();
        while($result->next()){
                $res = $result->current();


                array_push($dataInvest, array("dt" => $res['subs_start_dat'],
                                             "investment" => $res['subs_amount']
                                     ));
        }


        array_push($data, $dataInvest);

        return $data;

    }


    public function getReturnInvestment($accNum) {


        $sql = "select DATE_FORMAT(subs_start_dat, '%Y%m%d') as subs_start_dat,
                DATE_FORMAT(subs_end_dat, '%Y%m%d') as subs_end_dat from members where member_code = ? and subs_start_dat <= now() and subs_end_dat >= now() ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($accNum));

        
        while($result->next()){
                $res = $result->current();
                $start = $res["subs_start_dat"];
                $end = $res["subs_end_dat"];
        }
        

        $sql = "select count(1) as total
                from v_transaction
                where trans_type_code = 'Q201' and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d')
                between STR_TO_DATE('$start', '%Y%m%d') and STR_TO_DATE('$end', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and account_num1 = '$accNum'  ";
        }



        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows, 1);


        $sql = "select
                DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm,
                amount1 
                from v_transaction
                where trans_type_code = 'Q201' and
                STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between 
                STR_TO_DATE('$start', '%Y%m%d') and STR_TO_DATE('$end', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and  account_num1 = '$accNum' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataInvest = array();
        while($result->next()){
                $res = $result->current();
                

                array_push($dataInvest, array("dt" => $res['trans_dtm'],
                                             "amount" => $res['amount1'],
                                             "status" => "Paid"
                                     ));
        }


        array_push($data, $dataInvest);

        return $data;
    }
    

}
